IF OBJECT_ID('dbo.fnGetDegree') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetDegree
    IF OBJECT_ID('dbo.fnGetDegree') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetDegree >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetDegree >>>'
END
go

-- =======================================================================
-- Function Name: dbo.fnGetDegree()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns Comma-separated String of
--              Degrees for the given Person
--
-- Parameters:  @AdmPersonId = INT
--              
-- Returns:     VARCHAR(50) 
--
-- SampleCall:
-- SELECT @sdegrees = fnGetDegree ( 54172 )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 5/20/07     KC        Created
-- =======================================================================
CREATE FUNCTION dbo.fnGetDegree(
     @AdmPersonId INT
)
     RETURNS VARCHAR(50)
AS
BEGIN
     DECLARE   @degrees varchar(256),
               @add_deg varchar(50)

     DECLARE  Degs_cursor CURSOR 
     FOR SELECT RTRIM(D.Name)
     FROM   dbo.AdmDegree AS D INNER JOIN
            dbo.AdmDegree_AdmPerson as P ON D.Id = P.AdmDegreeId AND
            P.AdmPersonId = @AdmPersonId
     WHERE  D.Name IS NOT NULL AND RTRIM(D.Name) != ''       

     OPEN Degs_cursor

     FETCH NEXT FROM Degs_cursor INTO @add_deg

     SET  @degrees = @add_deg

     WHILE @@FETCH_STATUS = 0
     BEGIN
          FETCH NEXT FROM Degs_cursor INTO @add_deg

          IF @@FETCH_STATUS = 0
               SET  @degrees = IsNull(@degrees,'') + ', ' + @add_deg
     END       

     CLOSE Degs_cursor       
     DEALLOCATE  Degs_cursor       

     RETURN @degrees

END
go
IF OBJECT_ID('dbo.fnGetDegree') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetDegree >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetDegree >>>'
go
GRANT EXECUTE ON dbo.fnGetDegree TO InsightReport,Insight
go
